

ALTER      proc sp_get_transit_time_for_step (
@estimated_date int output,
@time_for_manual_process int, --  used while calculating fab-2-fg time
@mktg_part_number varchar(50),
@step_type_name varchar(50))

As
begin
	declare @previous_process_step_id bigint
	declare @temp_priority int
	declare @temp_location_id int
	declare @temp_previous_location_id int
	declare @temp_process_step_id bigint
	declare @temp_normal_cycle_time int
	declare @temp_express_cycle_time int
	declare @ship_time int
	declare @estimated_ship_date int
	declare @process_step_id bigint
	declare @process_type varchar(20)
	declare @process_type_name varchar(20)
	declare @temp_flow_cursor CURSOR
	declare @deallocate_flag int
	declare @return_bit int

	set @deallocate_flag = 0
	set dateformat mdy
	set @estimated_ship_date = 0
	set @return_bit = 1

	select @temp_process_step_id = ProcessStep_ID from Product where P_Marketing_Part_Number = @mktg_part_number
	set @temp_flow_cursor = CURSOR for
	select * from fn_get_process_flow_by_id (@temp_process_step_id)

	open @temp_flow_cursor 
		fetch next from @temp_flow_cursor 
		into @process_step_id, @process_type, @process_type_name

		while @@fetch_status = 0
		begin
			set @deallocate_flag = 1
			if @process_type_name = 'Mark & Pack' and ((@return_bit = 1) or ((@return_bit = 0) and (@step_type_name like 'Mark%')))
			begin
				if (select count(*) from Process_Step_Location_Table where ProcessStep_ID = @process_step_id) > 1
				begin
					select @temp_priority = MAX(PSLT_Priority) from Process_Step_Location_Table where ProcessStep_ID = @process_step_id
					select @temp_location_id = Location_ID, @temp_normal_cycle_time = PSLT_Cycle_Time_Normal from Process_Step_Location_Table where PSLT_Priority = @temp_priority and ProcessStep_ID = @process_step_id
				end
				else
					begin
					select @temp_priority = PSLT_Priority, @temp_location_id = Location_ID from Process_Step_Location_Table where ProcessStep_ID = @process_step_id
				end

				select @ship_time = TT_Normal_Time from Transportation_Table where ProcessStep_ID = @previous_process_step_id and TT_Source_Location_ID = @temp_location_id and TT_Destination_Location_ID = @temp_previous_location_id
				if @ship_time is null
					begin
					set @ship_time = 0	
				end
					
				if @temp_normal_cycle_time is null
				begin
					set @temp_normal_cycle_time = 0	
				end

				set @estimated_ship_date = @ship_time + @temp_normal_cycle_time + @estimated_ship_date
				print 'in mark&pack = ' + convert (varchar, @estimated_ship_date)

				if @step_type_name like 'Mark%'
				begin
				set @return_bit = 0
				end

				set @temp_previous_location_id = @temp_location_id
				set @previous_process_step_id = @process_step_id
			end			
	
			else if @process_type_name = 'Class Store' and ((@return_bit = 1) or ((@return_bit = 0) and (@step_type_name = 'Wafer Store')))
			begin
				if (select count(*) from Process_Step_Location_Table where ProcessStep_ID = @process_step_id) > 1
				begin
					select @temp_priority = MAX(PSLT_Priority) from Process_Step_Location_Table where ProcessStep_ID = @process_step_id
					select @temp_location_id = Location_ID from Process_Step_Location_Table where PSLT_Priority = @temp_priority and ProcessStep_ID = @process_step_id
				end
		
				else
				begin
					select @temp_priority = PSLT_Priority, @temp_location_id = Location_ID from Process_Step_Location_Table where ProcessStep_ID = @process_step_id
				end
			
				select @ship_time = TT_Normal_Time from Transportation_Table where ProcessStep_ID = @previous_process_step_id and TT_Source_Location_ID = @temp_location_id and TT_Destination_Location_ID = @temp_previous_location_id
				if @ship_time is null
				begin
					set @ship_time = 0	
				end
		
				select @temp_normal_cycle_time=IST_Max_Time from Inventory_Step_Table where ProcessStep_ID = @process_step_id 				
				if @temp_normal_cycle_time is null
				begin
					set @temp_normal_cycle_time = 0	
				end
		
				set @estimated_ship_date = @ship_time + @temp_normal_cycle_time  + @estimated_ship_date
				print 'in class store = ' + convert (varchar, @estimated_ship_date)

				if @step_type_name = 'Class Store'
				begin
				set @return_bit = 0
				end

				set @temp_previous_location_id = @temp_location_id
				set @previous_process_step_id = @process_step_id
			end
	
			else if @process_type_name = 'TEST' and ((@return_bit = 1) or ((@return_bit = 0) and (@step_type_name = 'Test')))
			begin
				if (select count(*) from Process_Step_Location_Table where ProcessStep_ID = @process_step_id) > 1
				begin
					select @temp_priority = MAX(PSLT_Priority) from Process_Step_Location_Table where ProcessStep_ID = @process_step_id
					select @temp_location_id = Location_ID, @temp_normal_cycle_time = PSLT_Cycle_Time_Normal from Process_Step_Location_Table where PSLT_Priority = @temp_priority and ProcessStep_ID = @process_step_id
				end
				else
				begin
					select @temp_priority = PSLT_Priority, @temp_location_id = Location_ID, @temp_normal_cycle_time = PSLT_Cycle_Time_Normal from Process_Step_Location_Table where ProcessStep_ID = @process_step_id
				end
		
				select @ship_time = TT_Normal_Time from Transportation_Table where ProcessStep_ID = @previous_process_step_id and TT_Source_Location_ID = @temp_location_id and TT_Destination_Location_ID = @temp_previous_location_id
				if @temp_normal_cycle_time is null
				begin
					set @temp_normal_cycle_time = 0
				end
	
				if @ship_time is null
				begin
					set @ship_time = 0	
				end
	
				set @estimated_ship_date = @ship_time + @temp_normal_cycle_time + @estimated_ship_date
				print 'in test = ' + convert (varchar, @estimated_ship_date)
				print 'ship time = ' + convert (varchar, @ship_time) + ', normal cycle time = ' + convert (varchar, @temp_normal_cycle_time)

				if @step_type_name = 'Test'
				begin
				set @return_bit = 0
				end

				set @temp_previous_location_id = @temp_location_id
				set @previous_process_step_id = @process_step_id
			end

			else if @process_type_name = 'Assembly' and ((@return_bit = 1) or ((@return_bit = 0) and (@step_type_name = 'Assembly')))
			begin
				if (select count(*) from Process_Step_Location_Table where ProcessStep_ID = @process_step_id) > 1
				begin
					select @temp_priority = MAX(PSLT_Priority) from Process_Step_Location_Table where ProcessStep_ID = @process_step_id
					select @temp_location_id = Location_ID, @temp_normal_cycle_time = PSLT_Cycle_Time_Normal from Process_Step_Location_Table where PSLT_Priority = @temp_priority and ProcessStep_ID = @process_step_id
				end
				else
				begin
					select @temp_priority = PSLT_Priority, @temp_location_id = Location_ID, @temp_normal_cycle_time = PSLT_Cycle_Time_Normal from Process_Step_Location_Table where ProcessStep_ID = @process_step_id
				end
	
				select @ship_time = TT_Normal_Time from Transportation_Table where ProcessStep_ID = @previous_process_step_id and TT_Source_Location_ID = @temp_location_id and TT_Destination_Location_ID = @temp_previous_location_id
				if @temp_normal_cycle_time is null
				begin
					set @temp_normal_cycle_time = 0
				end
	
				if @ship_time is null
				begin
					set @ship_time = 0	
				end
	
				set @estimated_ship_date = @ship_time + @temp_normal_cycle_time + @estimated_ship_date
				print 'in assembly = ' + convert (varchar, @estimated_ship_date)

				if @step_type_name = 'Assembly'
				begin
				set @return_bit = 0
				end

				set @temp_previous_location_id = @temp_location_id
				set @previous_process_step_id = @process_step_id
	
			end
	
			else if @process_type_name = 'Die Bank' and ((@return_bit = 1) or ((@return_bit = 0) and (@step_type_name = 'Die Bank')))
			begin
				if (select count(*) from Process_Step_Location_Table where ProcessStep_ID = @process_step_id) > 1
				begin
					select @temp_priority = MAX(PSLT_Priority) from Process_Step_Location_Table where ProcessStep_ID = @process_step_id
					select @temp_location_id = Location_ID from Process_Step_Location_Table where PSLT_Priority = @temp_priority and ProcessStep_ID = @process_step_id
				end
				else
				begin
					select @temp_priority = PSLT_Priority, @temp_location_id = Location_ID from Process_Step_Location_Table where ProcessStep_ID = @process_step_id
				end
		
				select @ship_time = TT_Normal_Time from Transportation_Table where ProcessStep_ID = @previous_process_step_id and TT_Source_Location_ID = @temp_location_id and TT_Destination_Location_ID = @temp_previous_location_id
				if @ship_time is null
				begin
					set @ship_time = 0	
				end
				select @temp_normal_cycle_time=IST_Max_Time from Inventory_Step_Table where ProcessStep_ID = @process_step_id 
				
				if @temp_normal_cycle_time is null
				begin
					set @temp_normal_cycle_time = 0	
				end
		
	
				set @estimated_ship_date = @ship_time + @temp_normal_cycle_time + @estimated_ship_date
				print 'in die bank = ' + convert (varchar, @estimated_ship_date)

				if @step_type_name = 'Die Bank'
				begin
				set @return_bit = 0
				end

				set @temp_previous_location_id = @temp_location_id
				set @previous_process_step_id = @process_step_id
			end
	
			else if @process_type_name = 'SORT' and ((@return_bit = 1) or ((@return_bit = 0) and (@step_type_name = 'Sort')))
			begin
				if (select count(*) from Process_Step_Location_Table where ProcessStep_ID = @process_step_id) > 1
				begin
					select @temp_priority = MAX(PSLT_Priority) from Process_Step_Location_Table where ProcessStep_ID = @process_step_id
					select @temp_location_id = Location_ID, @temp_normal_cycle_time = PSLT_Cycle_Time_Normal from Process_Step_Location_Table where PSLT_Priority = @temp_priority and ProcessStep_ID = @process_step_id
				end
				else
				begin
					select @temp_priority = PSLT_Priority, @temp_location_id = Location_ID, @temp_normal_cycle_time = PSLT_Cycle_Time_Normal from Process_Step_Location_Table where ProcessStep_ID = @process_step_id
				end
	
				select @ship_time = TT_Normal_Time from Transportation_Table where ProcessStep_ID = @previous_process_step_id and TT_Source_Location_ID = @temp_location_id and TT_Destination_Location_ID = @temp_previous_location_id
				if @temp_normal_cycle_time is null
				begin
					set @temp_normal_cycle_time = 0
				end
				if @ship_time is null
				begin
					set @ship_time = 0	
				end

				set @estimated_ship_date = @ship_time + @temp_normal_cycle_time +  @estimated_ship_date
				print 'in sort = ' + convert (varchar, @estimated_ship_date)

				if @step_type_name = 'Sort'
				begin
				set @return_bit = 0
				end

				set @temp_previous_location_id = @temp_location_id
				set @previous_process_step_id = @process_step_id
			end
	
			else if @process_type_name = 'Wafer Store' and ((@return_bit = 1) or ((@return_bit = 0) and (@step_type_name = 'Wafer Store')))
			begin
				if (select count(*) from Process_Step_Location_Table where ProcessStep_ID = @process_step_id) > 1
				begin
					select @temp_priority = MAX(PSLT_Priority) from Process_Step_Location_Table where ProcessStep_ID = @process_step_id
					select @temp_location_id = Location_ID from Process_Step_Location_Table where PSLT_Priority = @temp_priority and ProcessStep_ID = @process_step_id
				end
				else
				begin
					select @temp_priority = PSLT_Priority, @temp_location_id = Location_ID from Process_Step_Location_Table where ProcessStep_ID = @process_step_id
				end
			
				select @ship_time = TT_Normal_Time from Transportation_Table where ProcessStep_ID = @previous_process_step_id and TT_Source_Location_ID = @temp_location_id and TT_Destination_Location_ID = @temp_previous_location_id
	
				if @ship_time is null
				begin
					set @ship_time = 0	
				end
				select @temp_normal_cycle_time=IST_Max_Time from Inventory_Step_Table where ProcessStep_ID = @process_step_id 
					
				if @temp_normal_cycle_time is null
				begin
					set @temp_normal_cycle_time = 0	
				end

				set @estimated_ship_date = @ship_time + @temp_normal_cycle_time + @estimated_ship_date
				print 'in wafer store = ' + convert (varchar, @estimated_ship_date)

				if @step_type_name = 'Wafer Store'
				begin
				set @return_bit = 0
				end

				set @temp_previous_location_id = @temp_location_id
				set @previous_process_step_id = @process_step_id
			end
	
			else if @process_type_name = 'FAB' and ((@return_bit = 1) or ((@return_bit = 0) and (@step_type_name = 'Fab')))
			begin
				if (select count(*) from Process_Step_Location_Table where ProcessStep_ID = @process_step_id) > 1
				begin
					select @temp_priority = MAX(PSLT_Priority) from Process_Step_Location_Table where ProcessStep_ID = @process_step_id
					select @temp_location_id = Location_ID, @temp_normal_cycle_time = PSLT_Cycle_Time_Normal from Process_Step_Location_Table where PSLT_Priority = @temp_priority and ProcessStep_ID = @process_step_id
				end
				else
				begin
					select @temp_priority = PSLT_Priority, @temp_location_id = Location_ID, @temp_normal_cycle_time = PSLT_Cycle_Time_Normal from Process_Step_Location_Table where ProcessStep_ID = @process_step_id
				end
	
				select @ship_time = TT_Normal_Time from Transportation_Table where ProcessStep_ID = @previous_process_step_id and TT_Source_Location_ID = @temp_location_id and TT_Destination_Location_ID = @temp_previous_location_id
				if @temp_normal_cycle_time is null
				begin
					set @temp_normal_cycle_time = 0
				end
				if @ship_time is null
				begin
					set @ship_time = 0	
				end
				-- commented by prabhanjan on November 11, 2003	as po_freq is a non-numeric field and this rises an exception.
				-- now @po_freq is not used to calculate the @estimated_ship_date.
				/*				
				declare @po_freq varchar(50)
				select @po_freq = FT_PO_Frequency from fab_table where processStep_ID = @process_step_id
				if @po_freq is null or @po_freq = 'asneeded'
					begin
					set @estimated_ship_date = @estimated_ship_date + @time_for_manual_process	-- manual transactions time before the fab process starts
					end
				else
					begin
					set @estimated_ship_date = @estimated_ship_date + convert (int, @po_freq) + @time_for_manual_process
					end		

				*/					
				set @estimated_ship_date = @estimated_ship_date + @time_for_manual_process	-- manual transactions time before the fab process starts

				set @estimated_ship_date = @ship_time + @temp_normal_cycle_time + @estimated_ship_date	
				print 'in fab = ' + convert (varchar, @estimated_ship_date)

				if @step_type_name = 'Fab'
				begin
				set @return_bit = 0
				end

				set @temp_previous_location_id = @temp_location_id
				set @previous_process_step_id = @process_step_id	
			end

			fetch next from @temp_flow_cursor 
			into @process_step_id, @process_type, @process_type_name
		end	
	
	endloop:
		if @deallocate_flag = 1
		begin
			close @temp_flow_cursor
			deallocate @temp_flow_cursor
		end
	
		set @estimated_date = @estimated_ship_date
		print 'Estimated Date = ' + convert(varchar, @estimated_date)
		select @estimated_date as Estimated_Time
		--return
end



